'''※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
File Name: SqlBase.py
Author: GID5564
Description: 
Version: 1.0
Created Time: 04/09/23-10:04:58
※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※'''
  
#!/usr/bin/python
# -*- coding: UTF-8 -*-

class SqlBase():
    def __init__(self, cursor, conn):
        self._cursor = cursor
        self._conn = conn
        
    def close(self) -> None:
        '''
        关闭游标对象和连接对象
        :param:NULL
        :return:None
        '''
        self._cursor.close()
        self._conn.close()

    def execute(self, sqlstring: str) -> list:
        '''
        执行SQL语句
        :param sqlstring: Sql语句
        :return: 返回结果
        '''
        self._cursor.execute(sqlstring)
        data = self._cursor.fetchall()
        return data
        
        
    def selectbycolumns(self, tbname: str, *columns: str) -> list:
        '''
        通过列名进行Select查询
        :param tbname: 表名
        :param columns: 需要查询的列名
        :return: 查询结果
        '''
        col = str(columns).replace("[", "").replace("]", "").replace("'", "").replace("(", "").replace(")", "")
        sqlstring = f"select {col} from {tbname} "
        if len(columns) == 0: sqlstring = f"select *from {tbname}"
        self._cursor.execute(sqlstring)
        data = self._cursor.fetchall()
        return data

    def selectbyexpression(self, tbname: str, expression: str) -> list:
        '''
        通过where子句表达式进行Select查询
        :param tbname: 表名
        :param expression:where子句
        :return: 查询结果
        '''
        sqlstring = f"select *from {tbname} where {expression}"
        self._cursor.execute(sqlstring)
        data = self._cursor.fetchall()
        return data
        
        
    def insertbyvalues(self, tbname: str, *values) -> None:
        '''
        通过全部字段值新增数据到表
        :param tbname: 表名
        :param values: 所有字段的值
        :return: None
        '''
        vls = str(values).replace("[", "").replace("]", "")
        sqlstring = f"insert into {tbname} values {vls}"
        print(sqlstring)
        self._cursor.execute(sqlstring)
        self._conn.commit()

    def insertbykeyvalues(self, tbname: str, **keyvalues) -> None:
        '''
        通过 字段名=值 的键值对新增记录
        :param tbname: 表名
        :param keyvalues: 字段名=值的字典
        :return: None
        '''
        keys = str(keyvalues.keys()).replace("dict_keys", "").replace("'", "").replace("[", "").replace("]", "")
        values = str(keyvalues.values()).replace("dict_keys", "").replace("[", "").replace("]", "")
        sqlstring = f"insert into {tbname} {keys} values {values}"
        self._cursor.execute(sqlstring)
        self._conn.commit()    
        
        
    def deletebykeyvalues(self, tbname: str, **keyvalues) -> None:
        '''
        通过 字段名=值 的方式查找到对于键值对并删除
        :param tbname: 表名
        :param keyvalues: 键值对
        :return: None
        '''
        keys = list(keyvalues.keys())
        values = list(keyvalues.values())
        pairs = []
        for i in range(len(keys)):
            pairs.append(f"{keys[i]}={values[i]}")
            pairs.append("and")  # 使用and连接词
        del pairs[len(pairs) - 1]  # 删除最后一个and连接词
        pairs = str(pairs).replace("[", "").replace("]", "").replace("'", "").replace(",", "")
        sqlstring = f"delete from {tbname} where {pairs}"
        self._cursor.execute(sqlstring)
        self._conn.commit()

    def deletebyexpression(self, tbname: str, expression: str) -> None:
        '''
        通过where表达式进行查询并删除
        :param tbname: 表名
        :param expression:表达式
        :return: None
        '''
        sqlstring = f"delete from {tbname} where {expression}"
        self._cursor.execute(sqlstring)
        self._conn.commit()
   
        
        
    def updatebykeyvalues(self, tbname: str, expression: str, **keyvalues) -> None:
        '''
        通过expression表达式找到数据后对 字段名=值 进行修改
        :param tbname: 表名
        :param expression:where表达式
        :param keyvalues: 修改的字段名=值对
        :return: None
        '''
        keys = list(keyvalues.keys())
        values = list(keyvalues.values())
        keypairs = []
        for i in range(len(keys)):
            temp = f"{keys[i]}=\"{(values[i])}\""
            keypairs.append(temp)
        keypairs = str(keypairs).replace("[", "").replace("]", "").replace("'", "")
        sqlstring = f"update {tbname} set {keypairs} where {expression}"
        self._cursor.execute(sqlstring)
        self._conn.commit()    
        
        
        
    def createtable(self, tbname: str, *args: list) -> None:
        '''
        通过List创建新表格
        比如createtable("TB_TestTbale",["ID","nchar(10)"],["Password","nchar(20)","NOT NULL"])
        每个字段用一个list表示 顺序为 [字段名,类型名,*约束，*其他]
        :param tbname: 表格名称
        :param args: 参数
        :return:None
        '''
        data = []
        for i in range(len(args)):
            temp = str(args[i]).replace("[", "").replace("]", "").replace("'", "").replace(",", "")
            data.append(temp)
        data = str(data).replace("[", "(").replace("]", ")").replace("'", "")
        sqlstring = f"create table {tbname} {data}"
        self._cursor.execute(sqlstring)
        self._conn.commit()

        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        